#!/usr/bin/env python
# -*-coding:UTF-8-*-
import MySQLdb,ConfigParser,time,os

# 配置文件中OPTION 'enable' 说明: #
#  0 数据库 db #
#  1 迁移数据 #
#  2 删除旧数据库数据与结构 #
#  3 重新创建数据库 #


# 清空表格
def truncTables(conn, tables):
	tbls = tables.split(",")
	for tbl in tbls:
		if len(tbl) > 0:
			sql = "TRUNCATE TABLE %s" %(tbl)
			cur = conn.cursor()
			cur.execute(sql)
			conn.commit()
			print sql
			cur.close()


# 执SQL语句		
def execSQl(conn, sql, type = 1):
	if len(sql) > 0:
		cur = conn.cursor()
		if type == 2:
			cur.callproc(sql)
		else:
			cur.execute(sql)
		conn.commit()
		cur.close()

def deleteData(conn, type,items):
	its = items.split(",")
	for item in its:
		if len(item) > 0:
			sql = "DROP %s IF EXISTS %s " %(type, item)
			execSQl(conn, sql)
			print sql
		
		
# 计算表的记录数
def countTables(conn, tables):
	tbls = tables.split(",")
	sum = 0
	for tbl in tbls:
		if len(tbl) > 0:
			sql = "SELECT COUNT(*) FROM %s" %(tbl)
			cur = conn.cursor()
			cur.execute(sql)
			row = cur.fetchone()
			print "-*-%s(%s)" %(tbl, row[0])
			sum += int(row[0])
			cur.close()
	print "   Total[%s]: %s" %(tables,sum)
	

# 载入SQL文件爱你
def loadSqlFile(db_host, db_user, db_passwd, file):
	if len(file) > 0:
		mysql_cmd = '`which mysql`'
		sql_file = os.path.dirname(os.path.abspath(__file__))+os.sep+file;
		db_command="%s -u%s -p'%s' -h%s < %s" %(mysql_cmd,db_user,db_passwd,db_host,sql_file)
		if os.system(db_command) == 0:
			print "SOURCE %s ---> Finish." %(file)
		else:
			print "SOURCE %s ---> Error!" %(file)
	
if __name__ == "__main__":	
	t1 = time.time()
	# 1.读取配置文件
	cf = ConfigParser.ConfigParser()
	cf.read("kkyoo_gift.conf")

	# 2.读取MySQL配置，连接MySQL
	db_host=cf.get("db", "db_host")
	db_user=cf.get("db", "db_user")
	db_passwd=cf.get("db", "db_passwd")
	db_name=cf.get("db", "db_name")
	conn = MySQLdb.Connection(db_host, db_user, db_passwd, db_name)
	execSQl(conn, 'SET NAMES utf8')
	
	# 3.新建数据表结构
	print "\t\t001. Building News Tables ...."
	new_table=cf.get("new_table", "file")
	loadSqlFile(db_host, db_user, db_passwd, new_table)
	
	# 4.新建函数
	print "\t\t002. Creating News Functions ...."
	new_func=cf.get("new_function", "file")
	loadSqlFile(db_host, db_user, db_passwd, new_func)
	
	# 5.数据转移(type = 1)
	print "\t\t003. Changing Data To New System ...."
	sections = cf.sections();
	for section in sections:
		type = cf.getint(section, "enable")
		if type == 1: 
		#if section == 'k_gift_rank': 
			d1 = time.time()
			
			sql = cf.get(section, "sql")
			proc =  cf.get(section, "proc")
			ntable = cf.get(section, "ntable")
			otable = cf.get(section, "otable")
			desc = cf.get(section, "desc")
			
			print "####################################\t\n###  ",section,": ---> "
			truncTables(conn, ntable)
			execSQl(conn, sql)
			execSQl(conn, proc, 2)
			countTables(conn, otable)
			countTables(conn, ntable)
			
			d2 = time.time()
			d3 = d2 - d1;
			print "%ss Used.   %s END <--- \t\n" %(d3,desc)
	
	# 6.删除原有数据表、存储过程(type = 2)
	print "\t\t004. Deleting Old Tables and Procedures ...."
	for section in sections:
		type = cf.getint(section, "enable")
		if type == 2:
			d1 = time.time()
			type = cf.get(section, "type")
			items = cf.get(section, "items")
			desc = cf.get(section, "desc")
			print "####################################\t\n###  ",section,": ---> "
			deleteData(conn, type, items)
			d2 = time.time()
			d3 = d2 - d1;
			print "%ss Used.   %s END <--- \t\n" %(d3,desc)
	
	# 7.新建存储过程
	print "\t\t005. Create New Procedures ...."
	new_proc=cf.get("new_procedure", "file")
	loadSqlFile(db_host, db_user, db_passwd, new_proc)
	
	# 8.新建触发器
	print "\t\t006. Create New Triggers ...."
	new_trig=cf.get("new_trigger", "file")
	loadSqlFile(db_host, db_user, db_passwd, new_trig)
	
	conn.close()
	t2 = time.time()
	print "\t\tScript Completed!   Used:%ss." %(t2-t1)
# FILE END.